<?php
require_once(Mage::getBaseDir('lib')."/Excel/PHPExcel.php");
require_once(Mage::getBaseDir('lib')."/Excel/PHPExcel/Writer/Excel2007.php");

class Silk_Zgb_Block_Report_List extends Mage_Core_Block_Template
{
    public function getItemCollection()
    {
        $store_id = $this->getRequest()->getParam('store_id');
        $brand_id = $this->getRequest()->getParam('brand_id');
        $product_name = $this->getRequest()->getParam('product_name');
        $status = $this->getRequest()->getParam('status');
        $page = $this->getRequest()->getParam('page', 1);

        $export = $this->getRequest()->getParam('export');
        $apply = $this->getRequest()->getParam('apply');

        $session = Mage::getSingleton('admin/session');
        if($session->getUser()->getWebsiteId())
        {
            $websiteId = $session->getUser()->getWebsiteId();
            $store_id = Mage::getModel('core/website')->load($websiteId)->getDefaultStore()->getId();
        }
        $ids = array();
        $storeIds = array();

        $resource = Mage::getSingleton('core/resource');
        $db = $resource->getConnection('core_read');

        $collection = Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToSelect(array('name'))
            ->addAttributeToFilter('is_salt', array('eq'=>0));
        
        if($brand_id) $collection->addAttributeToFilter('brand', array('eq'=>$brand_id));
        if($store_id) 
        {
            $collection->addStoreFilter($store_id);
            if($status)
            {
                $collection->joinAttribute('status','catalog_product/status','entity_id',null,'left',$store_id);
                $collection->addAttributeToFilter('status', array('eq'=>$status));
            }
        }
        //echo $collection->getSelect();
        if($product_name) $collection->addAttributeToFilter('name',array('like'=>'%'.$product_name.'%'));
        if(!$export)
        {
            $collection->setCurPage($page);
            $collection->setPageSize(50);
        }
        //$collection->setPageSize(50);
        //var_dump($apply);
        if(!$apply && !$export)
        {
            $collection = array();
        }
        

        //echo $collection->getSelect();
        return $collection;
    }

    public function getCategoryByProduct($product)
    {
        $collection = $product->getCategoryCollection();
        //return "OK";
        $categoryName = "";
        foreach($collection as $item)
        {
            $category = Mage::getModel('catalog/category')->load($item->getId());;
            if($item->getLevel()>2)
            {
                return $category->getName();
            }
        }
        return $categoryName;
    }

    public function getCategorysByProduct($product)
    {
        $collection = $product->getCategoryCollection();
        //return "OK";
        $categoryNames = array();
        foreach($collection as $item)
        {
            $category = Mage::getModel('catalog/category')->load($item->getId());
            if (!$category->getIsActive()) continue;
            // if($category->getLevel()>2)
            // {

            // }
            if ($category->getLevel() == 3)
            {
                $categoryNames[0] = $category->getName();
            }
            if ($category->getLevel() == 4) {
                $categoryNames[1] = $category->getName();
            }
            if ($category->getLevel() == 5) {
                $categoryNames[2] = $category->getName();
            }
        }
        return $categoryNames;
    }

    public function exportToExcel()
    {
        $headers = array(
            'A' => array('name' => '商品ID', 'width' => 10),
            'B' => array('name' => '商品名称', 'width' => 50),
            'C' => array('name' => 'SKU', 'width' => 20),
            'D' => array('name' => '分类', 'width' => 15),
            'E' => array('name' => '地区', 'width' => 15),
            'F' => array('name' => '品牌', 'width' => 30),
            'G' => array('name' => '价格', 'width' => 12),
            'H' => array('name' => '库存', 'width' => 12),
            'I' => array('name' => '库存状态', 'width' => 12),
            'J' => array('name' => '商品状态', 'width' => 12),
            'K' => array('name' => '分类1', 'width' => 12),
            'L' => array('name' => '分类2', 'width' => 12),
        );


        $store_id = $this->getRequest()->getParam('store_id');
        $session = Mage::getSingleton('admin/session');
        $wbid = $session->getUser()->getWebsiteId();
        if($wbid)
        {
            $store_id = Mage::getModel('core/website')->load($wbid)->getDefaultStore()->getId();
        }
        $items = $this->getItemCollection();
        $objPHPExcel  = new PHPExcel();
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        $sheet = $objPHPExcel->getActiveSheet();
        $sheet->setTitle("商品清单");

        foreach ($headers as $key => $head) {
            $sheet->getColumnDimension($key)->setWidth($head['width']);
        }

        foreach ($headers as $key => $head) {
            $sheet->getStyle("{$key}1")->getFont()->setSize(13);
            $sheet->getStyle("{$key}1")->getFont()->setBold(true);
        }


        $sheet->getRowDimension('1')->setRowHeight(20);

        $styleThinBlackBorderOutline = array(
            'borders' => array (
               'outline' => array (
                  'style' => PHPExcel_Style_Border::BORDER_THIN, 
                  'color' => array ('argb' => 'FF000000'),
              ),
           ),
        );

        $statuses = array('pending'=>"待处理",'processing'=>"处理中",'complete'=>"已完成",'canceled'=>"已取消",'closed'=>'已关闭');

        $row = 1;

        //设置表头
        foreach ($headers as $key => $head) {
            $sheet->setCellValue("{$key}{$row}", $head['name']);
            $sheet->getStyle("{$key}{$row}")->applyFromArray($styleThinBlackBorderOutline);
        }

        $row++;
        foreach($items as $item)
        {
            $storeIds = $item->getStoreIds();
            if($storeIds)
            {
                foreach($storeIds as $storeId)
                {
                    //if($storeId==1) continue;
                    if($store_id && $storeId!=$store_id) continue;
                    $product = Mage::getModel('catalog/product')->setStoreId($storeId)->load($item->getId());
                    $status_id=null;
                    if($status_id && $product->getStatus()!=$status_id) continue;
                    $stockItem = Mage::getModel('cataloginventory/stock_item')->setStoreId($storeId)->loadByProduct($product);
                    $sheet->setCellValue("A{$row}", $product->getId());
                    $sheet->setCellValue("B{$row}", $product->getName());
                    $sheet->setCellValue("C{$row}", $product->getSku());
                    $names = $this->getCategorysByProduct($product);
                    $sheet->setCellValue("D{$row}", $names[0]);
                    $sheet->setCellValue("E{$row}", Mage::getModel('core/store')->load($storeId)->getName());
                    $sheet->setCellValue("F{$row}", $product->getAttributeText('brand'));
                    $sheet->setCellValue("G{$row}", $product->getPrice());
                    $sheet->setCellValue("H{$row}", intval($stockItem->getStockQty()));
                    $sheet->setCellValue("I{$row}", ($stockItem->getIsInStock() ? '有货' : '缺货'));
                    $sheet->setCellValue("J{$row}", ($product->getStatus() == 1 ? '已启用' : '已禁用'));


                    if (isset($names[1])) $sheet->setCellValue("K{$row}", $names[1]);
                    if ($names[2] == $names[0] || $names[1] == $names[0]) {
                        if (isset($names[2])) $sheet->setCellValue("L{$row}", '');
                    } else {
                        if (isset($names[2])) $sheet->setCellValue("L{$row}", $names[2]);
                    }


                    //设置表格线
                    foreach ($headers as $key => $head) {
                        $sheet->getStyle("{$key}{$row}")->applyFromArray($styleThinBlackBorderOutline);
                    }
                    

                    $row++;
                }
                
            }
            else
            {
                $product = Mage::getModel('catalog/product')->load($item->getId());
                if($status_id && $product->getStatus()!=$status_id) continue;
                $sheet->setCellValue("A{$row}", $product->getId());
                $sheet->setCellValue("B{$row}", $product->getName());
                $sheet->setCellValue("C{$row}", $product->getSku());
                $names = $this->getCategorysByProduct($product);
                $sheet->setCellValue("D{$row}", $names[0]);
                $sheet->setCellValue("E{$row}", '');
                $sheet->setCellValue("F{$row}", $product->getAttributeText('brand'));
                $sheet->setCellValue("G{$row}", $product->getPrice());
                $sheet->setCellValue("H{$row}",'');
                $sheet->setCellValue("I{$row}", '');
                $sheet->setCellValue("J{$row}", ($product->getStatus() == 1 ? '已启用' : '已禁用'));


                if (isset($names[1])) $sheet->setCellValue("K{$row}", $names[1]);
                if (isset($names[2])) $sheet->setCellValue("L{$row}", $names[2]);

                //设置表格线
                foreach ($headers as $key => $head) {
                    $sheet->getStyle("{$key}{$row}")->applyFromArray($styleThinBlackBorderOutline);
                }

                $row++;
            }     
            
            //$row++;
        }

        return $objWriter;
    }

    public function convertDate($date)
    {
        $timestamp = strtotime($date);
        return date("Y年m月d日 H:i", $timestamp+3600*8);
    }


    public function getDealerProductIds($id)
    {
        $resource = Mage::getSingleton('core/resource');
        $db = $resource->getConnection('core_read');
        $attribute_id = Silk_Zgb_Helper_Data::DEALER_ID;
        $sql = "select distinct(entity_id) from catalog_product_entity_int where attribute_id='{$attribute_id}' and `value`='{$id}'";
        $query = $db->query($sql);
        $items = $query->fetchAll();
        foreach($items as $item)
        {
            $ids[] = $item['entity_id'];
        }
        return $ids;
    }

    public function getManufacturerProductIds($id)
    {
        $resource = Mage::getSingleton('core/resource');
        $db = $resource->getConnection('core_read');
        $attribute_id = Silk_Zgb_Helper_Data::MANUFACTURER_ID;
        $sql = "select distinct(entity_id) from catalog_product_entity_int where attribute_id='{$attribute_id}' and `value`='{$id}'";
        echo $sql;
        $query = $db->query($sql);
        $items = $query->fetchAll();
        foreach($items as $item)
        {
            $ids[] = $item['entity_id'];
        }
        return $ids;
    }
}